package models
import (
	"database/sql"
	//"fmt"
	_ "github.com/mattn/go-sqlite3"
	"log"
    "io/ioutil"
    "strings"
)

type Img struct {
	Id int
	Fname string
	Tag string
	Info string
	Done int
    Top int
    Left int
    Width int
    Height int
}

type appContext struct {
    db *sql.DB
}

func ConnectSqlite()(*appContext, string) {
	return connectDB("sqlite3", "abc.db")
}


func connectDB(driverName string, dbName string) (*appContext, string) {
    db, err := sql.Open(driverName, dbName)
    if err != nil {
        return nil, err.Error()
    }
    if err = db.Ping(); err != nil {
        return nil, err.Error()
    }
    return &appContext{db}, ""
}

func (c *appContext) InitDB() {
	sql := `create table imgs (id INTEGER PRIMARY KEY, fname text,tag text, info text, done int default 0,top int default 0,left int default 0,width int default 100,height int default 100);`
    c.db.Exec(sql)
    count := c.Count()
    if count == 0{
        c.Img2Db("static/images","")
    }
}

func (c *appContext) Create(fname string) {
    stmt, err := c.db.Prepare("INSERT INTO imgs(fname,tag,info,done) values(?,'','',0)")
    if err != nil {
        log.Fatal(err)
    }
    result, err := stmt.Exec(fname)
    if err != nil {
        log.Fatal(err)
        //fmt.Printf("add error: %v", err)
        return
    }
    _, err = result.LastInsertId()
    if err != nil {
        log.Fatal(err)
    }
    //return lastID
    //fmt.Println("inserted id is ", lastID)
}

func (c *appContext) Count() int {
    rows, err := c.db.Query("SELECT count(*) as n FROM imgs")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    var n int
    if rows.Next() {
        err := rows.Scan(&n)
        if err != nil {
            log.Fatal(err)
        }
    }
    return n
}

func (c *appContext) ReadOne(index int) *Img {
	stmt, err := c.db.Prepare("SELECT * FROM imgs order by id limit ?, 1")
	if err != nil {
        log.Fatal(err)
    }
    rows, err := stmt.Query(index -1 )
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    p := new(Img)
    if rows.Next() {
        err := rows.Scan(&p.Id,&p.Fname, &p.Tag, &p.Info, &p.Done,&p.Top, &p.Left, &p.Width, &p.Height)
        if err != nil {
            log.Fatal(err)
            //fmt.Println(err)
        }
    }
    return p
    //fmt.Println(p.id, p.name, p.age)
}

// UPDATE
func (c *appContext) Update(tag string,info string,top int,left int,width int,height int,id int) {
    stmt, err := c.db.Prepare("UPDATE imgs SET tag = ?,info=?,top=?,left=?,width=?,height=?,done=0 WHERE id = ?")
    if err != nil {
        log.Fatal(err)
    }
    result, err := stmt.Exec(tag,info,top,left,width,height,id)
    if err != nil {
        log.Fatal(err)
    }
    _, err = result.RowsAffected()
    if err != nil {
        log.Fatal(err)
    }
    //fmt.Println("update affect rows is ", affectNum)
}

func (c *appContext) Img2Db(dirPth string, suffix string) {
    dir, err := ioutil.ReadDir(dirPth)
    if err != nil {
        return 
    }
    suffix = strings.ToUpper(suffix) 
    for _, fi := range dir {
        if fi.IsDir() { // 忽略目录
            continue
        }
        if strings.HasSuffix(strings.ToUpper(fi.Name()), suffix) { //匹配文件
            c.Create(fi.Name())
        }
    }
}

// func Test() {
// 	//log.Fatal("this is Test")
// 	os.Remove("./foo.db")

// 	db, err := sql.Open("sqlite3", "./foo.db")
// 	if err != nil {
// 		log.Fatal(err)
// 	}
// 	defer db.Close()

// 	sql := `create table imgs (id INTEGER PRIMARY KEY, fname text,tag text, info text, done int default 0);`
// 	db.Exec(sql)
// 	sql = `insert into imgs(fname,tag,info,done) values(1,1,1,0);`
// 	db.Exec(sql)
// 	sql = `insert into imgs(fname,tag,info,done) values(1,1,1,0);`
// 	db.Exec(sql)
// 	rows, err := db.Query("select * from imgs")
// 	if err != nil {
// 		log.Fatal(err)
// 	}
// 	defer rows.Close()
// 	var imgs []Img = make([]Img, 0)
// 	for rows.Next() {
// 		var u Img
// 		rows.Scan(&u.UserId, &u.Uname)
// 		users = append(imgs, u)
// 	}
// 	return users
// }

// func GetImg()
// func Get() {
// 	db, err := sql.Open("sqlite3", "./foo.db")
// 	if err != nil {
// 		log.Fatal(err)
// 	}
// 	defer db.Close()

// }
